package com.ncmem.up6.sql;

import com.ncmem.up6.database.DbHelper;
import com.ncmem.up6.model.DataBaseAttribute;
import com.ncmem.up6.utils.ConfigReader;
import com.ncmem.up6.utils.DataBaseType;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import software.amazon.awssdk.services.quicksight.model.DataSet;
import sun.security.krb5.Config;

import java.lang.reflect.Field;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class SqlTable {
    public String name;
    public List<SqlParam> m_fields;
    public Map<String, SqlParam> m_fieldsDic;

    public static SqlTable build(String name){
        return new SqlTable(name);
    }

    public SqlTable(String name) {
        this.name = name;
        this.m_fields = new ArrayList<SqlParam>();
        this.m_fieldsDic = new HashMap<String, SqlParam>();

        DataBaseType dbt = ConfigReader.dbType();
        ConfigReader r = new ConfigReader();
        JSONObject o = r.module(String.format("database.%s", name));
        JSONArray fields = o.getJSONArray("fields");
        for (int i = 0; i < fields.size(); ++i) {
            JSONObject f = fields.getJSONObject(i);
            SqlParam p = new SqlParam();
            p.m_name = f.getString("name");
            p.m_type = f.getString("type");
            p.primary = f.getBoolean("primary");
            p.length = f.getInt("length");
            p.identity = f.getBoolean("identity");
            p.m_dbType = dbt;
            this.m_fields.add(p);
            this.m_fieldsDic.put(p.m_name, p);
        }
    }

    public SqlParam primaryKey() {
        for (SqlParam p : this.m_fields) {
            if (p.primary) return p;
        }
        return null;
    }

    public SqlParam[] all() {
        return this.m_fields.toArray(new SqlParam[this.m_fields.size()]);
    }

    public Map<String, SqlParam> allDic() {
        return this.m_fieldsDic;
    }

    public SqlParam[] sel(String names) {
        List<SqlParam> ps = new ArrayList<SqlParam>();
        Map<String, Boolean> dic = new HashMap<String, Boolean>();
        String[] arr = names.split(",");
        for (String a : arr) {
            if (this.m_fieldsDic.containsKey(a.trim())) {
                ps.add(this.m_fieldsDic.get(a.trim()));
            }
        }
        return ps.toArray(new SqlParam[ps.size()]);
    }

    /**
     * 合并变量值
     *
     * @param sp
     * @return
     */
    public SqlParam[] mergeVal(SqlParam[] sp) throws ParseException {
        List<SqlParam> ps = new ArrayList<SqlParam>();
        for (SqlParam p : sp) {
            if (this.m_fieldsDic.containsKey(p.m_name)) {
                SqlParam v = this.m_fieldsDic.get(p.m_name);
                v.m_valTm = p.m_valTm;
                v.m_valBool = p.m_valBool;
                v.m_valByte = p.m_valByte;
                v.m_valShort = p.m_valShort;
                v.m_valInt = p.m_valInt;
                v.m_valLong = p.m_valLong;
                v.m_valStr = p.m_valStr;
                ps.add(v);
            }
        }
        return ps.toArray(new SqlParam[ps.size()]);
    }

    /**
     * 从对象中提取与数据表绑定的字段，并自动将对象值设置到字段中。
     *
     * @param o
     * @param igoIdentity 是否忽略自增类型字段
     * @return
     * @throws IllegalAccessException
     * @throws ParseException
     */
    private SqlParam[] extractFields(Object o, Boolean igoIdentity) throws IllegalAccessException, ParseException {
        List<SqlParam> fields = new ArrayList<SqlParam>();
        Field[] fs = o.getClass().getFields();
        for (int i = 0; i < fs.length; ++i) {
            if (fs[i].isAnnotationPresent(DataBaseAttribute.class)) {
                DataBaseAttribute a = fs[i].getAnnotation(DataBaseAttribute.class);
                //有此字段
                if (this.m_fieldsDic.containsKey(a.name())) {
                    SqlParam p = this.m_fieldsDic.get(a.name());
                    p.setValue(fs[i].get(o));
                    //忽略自增字段
                    if (igoIdentity && p.identity) continue;
                    fields.add(p);
                }
            }
        }
        return fields.toArray(new SqlParam[fields.size()]);
    }

    /**
     * 取主键外的所有键
     * @param o
     * @return
     * @throws IllegalAccessException
     * @throws ParseException
     */
    private SqlParam[] extractWithOutPK(Object o) throws IllegalAccessException, ParseException {
        List<SqlParam> fields = new ArrayList<SqlParam>();
        Field[] fs = o.getClass().getFields();
        for (int i = 0; i < fs.length; ++i) {
            if (fs[i].isAnnotationPresent(DataBaseAttribute.class)) {
                DataBaseAttribute a = fs[i].getAnnotation(DataBaseAttribute.class);
                //有此字段
                if (this.m_fieldsDic.containsKey(a.name())) {
                    SqlParam p = this.m_fieldsDic.get(a.name());
                    p.setValue(fs[i].get(o));
                    //忽略主键
                    if ( p.primary) continue;
                    fields.add(p);
                }
            }
        }
        return fields.toArray(new SqlParam[fields.size()]);
    }

    /**
     * 从对象中提取指定名称的字段数据
     * @param o
     * @param names 指定字段列表
     * @return
     * @throws IllegalAccessException
     * @throws ParseException
     */
    private SqlParam[] extractFields(Object o, String names) throws IllegalAccessException, ParseException {
        SqlParam[] ps = this.sel(names);
        Map<String,SqlParam> dic = new HashMap<String,SqlParam>();
        for (int i= 0 ; i < ps.length;++i) dic.put(ps[i].m_name,ps[i]);

        List<SqlParam> fields = new ArrayList<SqlParam>();
        Field[] fs = o.getClass().getFields();
        for (int i = 0; i < fs.length; ++i) {
            if (fs[i].isAnnotationPresent(DataBaseAttribute.class)) {
                DataBaseAttribute a = fs[i].getAnnotation(DataBaseAttribute.class);
                //有此字段
                if (dic.containsKey(a.name())) {
                    SqlParam p = dic.get(a.name());
                    p.setValue(fs[i].get(o));
                    //忽略自增字段
                    fields.add(p);
                }
            }
        }
        return fields.toArray(new SqlParam[fields.size()]);
    }
    public void bind(PreparedStatement cmd, SqlParam[] ps) throws ParseException, SQLException {
        SqlParam[] vals = this.mergeVal(ps);
        for (int i = 0, l = vals.length; i < l; ++i) {
            vals[i].bind(cmd, i + 1);
        }
    }
    public void bind(PreparedStatement cmd, SqlParam[] ps,int indexBegin) throws ParseException, SQLException {
        SqlParam[] vals = this.mergeVal(ps);
        for (int i = 0, l = vals.length; i < l; ++i) {
            vals[i].bind(cmd, indexBegin++);
        }
    }

    public <T> void insert(T t) throws ParseException, IllegalAccessException, SQLException {
        SqlParam[] fs = this.extractFields(t, true);
        String sql = SqlBuilder.build().insert(this.name, fs);
        DbHelper db = new DbHelper();
        PreparedStatement cmd = db.GetCommand(sql);
        for (int i = 0, l = fs.length; i < l; ++i) {
            fs[i].bind(cmd, i + 1);
        }
        db.ExecuteNonQuery(cmd);
    }
    /**
     * 插入一条数据，仅添加指定字段数据
     * @param names
     * @param o
     */
    public void insert(String names,Object o) throws ParseException, IllegalAccessException, SQLException {
        SqlParam[] fs = this.extractFields(o, names);
        String sql = SqlBuilder.build().insert(this.name, fs);

        DbHelper db = new DbHelper();
        PreparedStatement cmd = db.GetCommand(sql);
        for (int i = 0, l = fs.length; i < l; ++i) {
            fs[i].bind(cmd, i + 1);
        }
        db.Execute(cmd);
    }
    public <T> void inserts(T[] arr) throws ParseException, IllegalAccessException, SQLException {
        if (arr.length == 0) return;
        //提取所有字段和值（忽略自增字段）
        SqlParam[] fields = this.extractFields(arr[0],true);
        String sql = SqlBuilder.build().insert(this.name, fields);

        DbHelper db = new DbHelper();
        PreparedStatement cmd = db.GetCommand(sql);

        for (T a : arr)
        {
            //提取对象所有数据表字段，排队自增列
            SqlParam[] pars = this.extractFields(a,true);
            int i = 1;
            for(SqlParam p : pars)
            {
                p.bind(cmd,i++);
            }
            cmd.executeUpdate();
        }

        cmd.close();
    }
    public <T> void inserts(List<T> arr) throws ParseException, IllegalAccessException, SQLException {
        if (arr.size() == 0) return;
        //提取所有字段和值（忽略自增字段）
        SqlParam[] fields = this.extractFields(arr.get(0),true);
        String sql = SqlBuilder.build().insert(this.name, fields);

        DbHelper db = new DbHelper();
        PreparedStatement cmd = db.GetCommand(sql);

        for (int i = 0 ; i < arr.size();++i)
        {
            //提取对象所有数据表字段，排队自增列
            SqlParam[] pars = this.extractFields(arr.get(i),true);
            int col = 1;
            for(SqlParam p : pars)
            {
                p.bind(cmd,col++);
            }
            cmd.executeUpdate();
        }

        cmd.close();
    }

    public <T> T read(T t,SqlWhere where) throws ParseException, IllegalAccessException, SQLException {
        SqlParam[] fs = this.extractFields(t,false);
        String sql = SqlBuilder.build().select(this.name,fs,where);
        DbHelper db = new DbHelper();
        PreparedStatement cmd = db.GetCommand(sql);
        where.bind(this,cmd);
        ResultSet ds = db.ExecuteDataSet(cmd);
        if(ds.next())
        {
            SqlValSetter.build().read(ds,fs,t);
        }
        return t;
    }
    public <T> T readOne(T t,SqlWhere w) throws ParseException, IllegalAccessException, SQLException {
        SqlParam[] fs = this.extractFields(t,false);
        String sql = SqlBuilder.build().select(this.name,fs,w,1);
        DbHelper db = new DbHelper();
        PreparedStatement cmd = db.GetCommand(sql);
        w.bind(this,cmd);
        ResultSet ds = db.ExecuteDataSet(cmd);
        if(ds.next())
        {
            SqlValSetter.build().read(ds,fs,t);
        }
        else{t=null;}
        return t;
    }
    public <T> T readOne(T t,String cols,SqlWhere w) throws ParseException, IllegalAccessException, SQLException {
        SqlParam[] fs = this.sel(cols);
        String sql = SqlBuilder.build().select(this.name,fs,w,1);
        DbHelper db = new DbHelper();
        PreparedStatement cmd = db.GetCommand(sql);
        w.bind(this,cmd);
        ResultSet ds = db.ExecuteDataSet(cmd);
        if(ds.next())
        {
            SqlValSetter.build().read(ds,fs,t);
        }
        else{t=null;}
        return t;
    }

    public <T> List<T> reads(T t,SqlWhere w) throws ParseException, IllegalAccessException, SQLException, InstantiationException {
        List<T> fs = new ArrayList<T>();
        SqlParam[] ps = this.extractFields(t,false);
        String sql = SqlBuilder.build().select(this.name,ps,w);
        DbHelper db = new DbHelper();
        PreparedStatement cmd = db.GetCommand(sql);
        w.bind(this,cmd);
        ResultSet ds = db.ExecuteDataSet(cmd);
        if(null==ds) return fs;
        SqlValSetter st = SqlValSetter.build();
        while(ds.next())
        {
            Object o = t.getClass().newInstance();
            st.read(ds,ps,o);
            fs.add((T) o);
        }
        return fs;
    }

    /**
     * 从数据表中读取指定的列
     * @param t
     * @param cols
     * @param w
     * @param <T>
     * @return
     * @throws ParseException
     * @throws IllegalAccessException
     * @throws SQLException
     * @throws InstantiationException
     */
    public <T> List<T> reads(T t,String cols,SqlWhere w) throws ParseException, IllegalAccessException, SQLException, InstantiationException {
        List<T> fs = new ArrayList<T>();
        SqlParam[] ps = this.sel(cols);
        String sql = SqlBuilder.build().select(this.name,ps,w);
        DbHelper db = new DbHelper();
        PreparedStatement cmd = db.GetCommand(sql);
        w.bind(this,cmd);
        ResultSet ds = db.ExecuteDataSet(cmd);
        SqlValSetter st = SqlValSetter.build();
        while(ds.next())
        {
            Object o = t.getClass().newInstance();
            st.read(ds,ps,o);
            fs.add((T) o);
        }
        return fs;
    }

    public <T> List<T> reads(T t,String cols,SqlWhere w,SqlSort sort) throws ParseException, IllegalAccessException, SQLException, InstantiationException {
        List<T> fs = new ArrayList<T>();
        SqlParam[] ps = this.sel(cols);
        String sql = SqlBuilder.build().select(this.name,ps,w,sort);
        DbHelper db = new DbHelper();
        PreparedStatement cmd = db.GetCommand(sql);
        w.bind(this,cmd);
        ResultSet ds = db.ExecuteDataSet(cmd);
        SqlValSetter st = SqlValSetter.build();
        while(ds.next())
        {
            Object o = t.getClass().newInstance();
            st.read(ds,ps,o);
            fs.add((T) o);
        }
        return fs;
    }

    public <T> void update(T t,SqlWhere where) throws ParseException, IllegalAccessException, SQLException {
        SqlParam[] fields = this.extractFields(t,true);
        String sql = SqlBuilder.build().update(this.name,fields,where);
        DbHelper db = new DbHelper();
        PreparedStatement cmd = db.GetCommand(sql);
        this.bind(cmd,fields);
        where.bind(this,cmd,fields.length+1);
        db.ExecuteNonQuery(cmd);
    }
    /**
     * 根据主键更新所有列
     * @param t
     * @param <T>
     * @throws ParseException
     * @throws IllegalAccessException
     * @throws SQLException
     */
    public <T> void update(T t) throws ParseException, IllegalAccessException, SQLException {
        SqlParam[] fields = this.extractWithOutPK(t);
        SqlParam[] pks = new SqlParam[]{this.primaryKey()};
        String sql = SqlBuilder.build().update(this.name,fields,pks);
        DbHelper db = new DbHelper();
        PreparedStatement cmd = db.GetCommand(sql);
        this.bind(cmd,fields);
        pks[0].bind(cmd,fields.length+1);
        db.ExecuteNonQuery(cmd);
    }

    /**
     * 根据对象中的字段条件更新指定列值
     * @param arr
     * @param st
     * @param colWs 条件列
     * @param <T>
     * @throws ParseException
     * @throws IllegalAccessException
     * @throws SQLException
     */
    public <T> void updates(List<T> arr,SqlSeter st, String colWs) throws ParseException, IllegalAccessException, SQLException {
        if(arr.size()==0) return;
        SqlParam[] columns = this.extractFields(arr.get(0),colWs);
        String sql = SqlBuilder.build().update(this.name,st,columns);
        DbHelper db = new DbHelper();
        PreparedStatement cmd = db.GetCommand(sql);
        for (int i = 0 ; i < arr.size();++i)
        {
            st.bind(this,cmd);
            //提取字段值
            columns = this.extractFields(arr.get(i),colWs);
            this.bind(cmd,columns,st.size()+1);
            cmd.executeUpdate();
        }
    }
    public void update(SqlSeter st, SqlParam[] where) throws ParseException, SQLException {
        String sql = SqlBuilder.build().update(this.name, st, where);

        DbHelper db = new DbHelper();
        PreparedStatement cmd = db.GetCommand(sql);
        //绑定更新变量
        this.bind(cmd, st.toArray());
        //绑定条件变量
        this.bind(cmd, where);
        db.ExecuteNonQuery(cmd);
    }
    public void update(SqlSeter st,SqlWhere w) throws ParseException, IllegalAccessException, SQLException {
        String sql = SqlBuilder.build().update(this.name,st,w);
        DbHelper db = new DbHelper();
        PreparedStatement cmd = db.GetCommand(sql);
        st.bind(this,cmd);
        w.bind(this,cmd,st.size()+1);
        db.ExecuteNonQuery(cmd);
    }

    /**
     *
     */
    public void clear()
    {
        String sql = "delete from " + this.name;
        if (ConfigReader.dbType() == DataBaseType.Oracle)
            sql = "truncate table "+this.name;

        DbHelper db = new DbHelper();
        PreparedStatement cmd = db.GetCommand(sql);
        db.ExecuteNonQuery(cmd);
    }

    public void del(SqlWhere w) throws SQLException, ParseException {
        String sql = SqlBuilder.build().delete(this.name,w);
        DbHelper db = new DbHelper();
        PreparedStatement cmd = db.GetCommand(sql);
        w.bind(this,cmd);
        db.ExecuteNonQuery(cmd);
    }
}
